﻿///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Net.Mail;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class Merit_Pay_140418 : Bonus
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            //////只允许顾问、文案、OM、总部会计、Admin 访问
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_counsellor"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Counsellor && !bIs_Writer && !bIs_O_M && !bIs_Admin && !bIs_Accountant)
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面

                //暂时关闭
                Bt_Bonus_Exel.Enabled = false;

                //////顾问、文案，设定姓名，关闭选项
                DDList_Counsellor_Writer.Enabled = true;
                DDList_Counsellor_Writer.Visible = true;
                if ((bIs_Counsellor || bIs_Writer))
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Accountant)
                            {
                                DDList_Counsellor_Writer.Enabled = false;
                                DDList_Counsellor_Writer.Visible = false;
                            }

                Init_Office_YearMonth();
                Init_CounsellorWriter();
                string strOffice = Request["Office"].ToString().Trim();
                strOffice = Common_SXD.get_C_Office(strOffice);
                string strCounsellor_Writer;
                if (DDList_Counsellor_Writer.Visible)
                {
                    strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
                }
                else
                    strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();
                List_Merit_Pay(strOffice, strCounsellor_Writer, TextBox_Year_Month.Text);
//                TextBox_Level_Counsellor.Text = tempLevel_Counsellor; 
                ///合同签约计算
                Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
                Init_Tables_Contract_Qianyue();
                Init_Tables_Contract_Tuikuan();
                Init_Contract_Amount();
            
            }



        }

        /// <summary>
        ///  初始化：顾问合同退约，退款
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次', '%联办'，'全程二次', '申请学校', '%签证', '%文书' 的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
        ///    2011年7月26日之前
        /// </newpara>
        /// <newpara>
        /// AA) '全程一次', '%联办'，'全程二次', '申请学校', '%签证', '%文书' 的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
        ///    2011年7月26日之后
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Contract_Tuikuan()
        {

            //清除表中的内容
            Table_Contract_Tuikuan.Rows.Clear();
            //			Table_Bonus1_Kou.Rows.Clear();
            J1_Kou = 0;
            SubTotal_Lines J1_KouFilling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";        
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#endif

            string strFilter = "";
            /// <newpara>
            ///A) '全程一次', '%联办'，'全程二次', '申请学校', '%签证', '%文书' 的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
            ///    2011年7月26日之前
            ///  </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' ) OR  Student_Type like '%联办'  OR ( Student_Type like '申请学校' ) OR  Student_Type like '%签证' OR Student_Type like '%文书' )  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J1_KouFilling_Table = Filling_Table_with_Heads(sSql + strFilter, J1_KouFilling_Table, 1, Table_Contract_Tuikuan);

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#endif

            strFilter = "";
            /// <newpara>
            ///AA) '全程一次', '%联办'，'全程二次', '申请学校', '%签证', '%文书' 的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
            ///    2011年7月26日之后
            ///  </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' ) OR  Student_Type like '%联办'  OR ( Student_Type like '申请学校' ) OR  Student_Type like '%签证' OR Student_Type like '%文书' )  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26' )";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J1_KouFilling_Table = Filling_Table(sSql + strFilter, J1_KouFilling_Table, 1, Table_Contract_Tuikuan);

            J1_Kou = J1_KouFilling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Contract_Tuikuan.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化: 顾问合同签约，收款
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 只有一期款发生在本月， 二期款发生在其它月份 
        /// </newpara>
        /// <newpara> 
        /// B) 只有二期款发生在本月， 一期款发生在其他月份 
        /// </newpara>
        /// <newpara> 
        /// C) 二期款、 一期款均发生在本月
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Contract_Qianyue()
        {

            //清除表中的内容
            Table_Contract_Qianyue.Rows.Clear();
            SubTotal_Lines J1_JiangReturn_Filling_Table = new SubTotal_Lines();

            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment as 'Shishoujinge'  from contract where ";        
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment as 'Shishoujinge'  from contract where ";
#endif
            string strFilter = "";
            ///////////////////////////////////////////////////
            /// <newpara> 
            /// A) 只有一期款发生在本月， 二期款发生在其它月份
            /// </newpara>

            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' ) OR  Student_Type like '%联办'  OR ( Student_Type like '申请学校' ) OR  Student_Type like '%签证' OR Student_Type like '%文书' ) AND counsellor = '" + strCounsellor_Writer + "'";

            string sOrder = "";
            sOrder = " order by " + "Series_No DESC";

            J1_JiangReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter + sOrder, J1_JiangReturn_Filling_Table, 1, Table_Contract_Qianyue);


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Second_Payment   from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Second_Payment   from contract where ";
#endif
            ///////////////////////////////////////////////////
            /// <newpara> 
            /// B) 二期款发生在本月， 一期款发生在其他月份 
            /// </newpara>
            strFilter = "";

            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment < '" + Common_SXD.strStartDate + "') OR (Date_First_payment > '" + Common_SXD.strEndingDate;
            strFilter += "'))  AND ( ( Student_Type like '全程%' ) OR  Student_Type like '%联办'  OR ( Student_Type like '申请学校' ) OR  Student_Type like '%签证' OR Student_Type like '%文书' )  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);		

            J1_JiangReturn_Filling_Table = Filling_Table(sSql + strFilter, J1_JiangReturn_Filling_Table, 1, Table_Contract_Qianyue);


#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment   from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment   from contract where ";
#endif
            ///////////////////////////////////////////////////
            /// <newpara> 
            /// C) 二期款、 一期款均发生在本月
            /// </newpara>
            strFilter = "";

            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment >= '" + Common_SXD.strStartDate + "') AND (Date_First_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( ( Student_Type like '全程%' ) OR  Student_Type like '%联办'  OR ( Student_Type like '申请学校' ) OR  Student_Type like '%签证' OR Student_Type like '%文书' ) AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J1_JiangReturn_Filling_Table = Filling_Table(sSql + strFilter, J1_JiangReturn_Filling_Table, 1, Table_Contract_Qianyue);


            J1_Jiang = J1_JiangReturn_Filling_Table.Sub_Total;
            ///<newpara>
            /// 生成奖1的小计	
            ///</newpara>
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Contract_Qianyue.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }


        /// <summary>
        /// 顾问、文案控件的初始化
        /// </summary>
        private void Init_CounsellorWriter()
        {

            string sDDList_Office = Request["Office"].ToString().Trim();
            sDDList_Office = Common_SXD.get_C_Office(sDDList_Office);

            ///SqlConnection sqlCnn = new SqlConnection(strCnn);

            // 载入顾问、文案数据，绑定到 DDList_Counsellor_Writer 中
            string sSql = "select * from Employees ";
            sSql += "where C_N_Employee like '?' or ( ( Is_Counsellor = 1 or  Is_Writer = 1 ) and Is_Active = 1 and Office ='" + sDDList_Office + "' ) ";
            sSql += "order by Code_Employee asc";

            Common_SXD.Init_DropDownList(DDList_Counsellor_Writer, "Employees", sSql);

        }

        /// <summary>
        /// 地区、计奖年、月的初始化
        /// </summary>
        private void Init_Office_YearMonth()
        {
            TextBox_Year_Month.Text = Common_SXD.TimeToYM(DateTime.Now.ToString());
        }

        decimal J1_Jiang, J1_Kou/*, JJ1*/;

        int iLevel_Counsellor; /* 顾问级别 */
        decimal dMerit_Pay; /* 绩效工资 */
        decimal dPercent_Frozen; /* 冻结百分比 */
        decimal dQuota_month; /* 月扣减合同额 */
        decimal dCountract_Amount_month; /* 月签约额 */
        decimal dGap_Amount_month;  /* 月缺口金额 */
        decimal dMerit_Pay_Frozen;  /* 冻结工资额 */
        decimal dTake_Home_Merit_Pay;  /* 实得绩效工资 */
        decimal dMerit_Pay_UnFrozen;  /* 解冻工资额 */

        int Max_TopPart = 0, Max_MiddlePart = 0, Max_BottomPart = 0;

        /// <summary>
        ///  初始化：当月签约额
        /// </summary>
        private void Init_Contract_Amount(/*string sTime*/)
        {
            ////////////////////////初始值为空
            Label_Total.Text = "";

            dCountract_Amount_month = J1_Jiang - J1_Kou;

            Label_Total.Text = dCountract_Amount_month.ToString().Trim();
            
        }

        /// <summary>
        /// 初始化顾问等级
        /// 根据顾问等级，计算出绩效工资、冻结百分比、月扣减合同额3个数据。
        /// </summary>
        /// <param name="myLevel"></param>
        private void Init_Level_Counsellor(string myLevel)
        {
            switch (myLevel)
            {
                case "1":
                    iLevel_Counsellor = 1; /* 顾问级别 */
	                dMerit_Pay = 6000; /* 绩效工资 */
	                dPercent_Frozen = 0.20m; /* 冻结百分比 */
	                dQuota_month = 40000; /* 月扣减合同额 */
	    
                    break;

                case "2":
                    iLevel_Counsellor = 2; /* 顾问级别 */
	                dMerit_Pay = 5000; /* 绩效工资 */
	                dPercent_Frozen = 0.20m; /* 冻结百分比 */
	                dQuota_month = 30000; /* 月扣减合同额 */
	    
                    break;
                case "3":
                    iLevel_Counsellor = 3; /* 顾问级别 */
	                dMerit_Pay = 4000; /* 绩效工资 */
	                dPercent_Frozen = 0.15m; /* 冻结百分比 */
	                dQuota_month = 20000; /* 月扣减合同额 */
	    
                    break;

                case "4":
                    iLevel_Counsellor = 4; /* 顾问级别 */
	                dMerit_Pay = 3000; /* 绩效工资 */
	                dPercent_Frozen = 0.10m; /* 冻结百分比 */
	                dQuota_month = 10000; /* 月扣减合同额 */
	    
                    break;

                default:
                    break;

            }
        }

        /// <summary>
        /// 绩效工资更新，每年的二至十二月，同时要求顾问级别没有改变。
        /// </summary>
        /// 
        private void Update_Merit_Pay(string myLevel_Counsellor, string myGap_Amout_month, string myMerit_Pay_Frozen, string myTake_Home_Merit_Pay)
        {
            Decimal dGap_Amount_last_month = Convert.ToDecimal(myGap_Amout_month);
            Decimal dTake_Home_Merit_Pay_last_month = Convert.ToDecimal(myTake_Home_Merit_Pay);
            dGap_Amount_month = dCountract_Amount_month - dQuota_month + dGap_Amount_last_month;
            
            if (dGap_Amount_month > 0)
                dGap_Amount_month = 0;

            if (dGap_Amount_month < 0)
                dMerit_Pay_Frozen = Convert.ToDecimal(myMerit_Pay_Frozen) +
                   dMerit_Pay * dPercent_Frozen;
            else
                dMerit_Pay_Frozen = 0;

            if (myLevel_Counsellor == "4" && dGap_Amount_last_month < 0 && dGap_Amount_month < 0 )
            {/// 4级顾问，如果上月缺口 < 0 同时本月缺口 < 0, 本月实得绩效 = 上月实得绩效工资90%
             /// 
                dTake_Home_Merit_Pay = 0.90m * dTake_Home_Merit_Pay_last_month;
            /// 当月冻结额 = 当月绩效- 当月实得绩效。
            /// 
                if (dTake_Home_Merit_Pay < 2000)
                    dTake_Home_Merit_Pay = 2000;

                dMerit_Pay_Frozen = dMerit_Pay - dTake_Home_Merit_Pay;
            }
            else
                dTake_Home_Merit_Pay = dMerit_Pay - dMerit_Pay_Frozen;  
            
        }

        /// <summary>
        /// 绩效工资初始化，每年的一月，或者更换了顾问级别
        /// </summary>
        /// 
        private void  Init_Merit_Pay()
        {
            dGap_Amount_month = dCountract_Amount_month - dQuota_month;

            if (dGap_Amount_month > 0)
                dGap_Amount_month = 0;

            if (dGap_Amount_month < 0)
                dMerit_Pay_Frozen = dMerit_Pay * dPercent_Frozen;
            else
                dMerit_Pay_Frozen = 0;

            dTake_Home_Merit_Pay = dMerit_Pay - dMerit_Pay_Frozen;
            dMerit_Pay_UnFrozen = 0;
           
        }
        

        /// <summary>
        /// 计算绩效工资
        /// </summary>
        /// <param name="myOffice"></param>
        /// <param name="myCounsellor"></param>
        /// <param name="sTime"></param>
        private void Calculation_Merit_Pay(string myOffice, string myCounsellor, string sTime)
        {
            ///string sSearchValue = TextBox_SearchValue.Text.Trim();

            ///string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            int Month_selected = 0; ///选定月份
            int Year_selected = 0; ///选定年份

            DateTime dNow = DateTime.Now;
            int tDay_now = DateTime.Now.Day;
            int tMonth_now = DateTime.Now.Month;
            int tYear_now = DateTime.Now.Year;
            Response.Clear();
           
            ///检查年月字符串语法
            Regex Testing = new Regex("[0-9]{4}(-|/){1}[0-9]{1,2}");
            if (Testing.IsMatch(sTime))
            {
                System.DateTime bonusDateTime = Convert.ToDateTime(sTime + "-1");
                if (bonusDateTime > Convert.ToDateTime("1930-1-1"))
                {
                    Month_selected = bonusDateTime.Month;
                    Year_selected = bonusDateTime.Year;

                }
            }
            
            ///只有在本月操作，才能计算绩效工资
            if(Month_selected != tMonth_now)
                return;
            if (Year_selected != tYear_now)
                return;
            

            string strLevel_Counsellor_LastMonth = "0";

            ///级别初始化
            Init_Level_Counsellor(TextBox_Level_Counsellor.Text);

            if (Month_selected == 1)
                Init_Merit_Pay();
            else
            {///读取上个月的顾问级别
                strLevel_Counsellor_LastMonth = Common_SXD.CHAR_MySQL("select Level_Counsellor from Merit_Pay ",
                    "Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                    "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + (Month_selected - 1).ToString().Trim() + "'", Response);
            
                if (strLevel_Counsellor_LastMonth == "ERROR") ///读取顾问等级出错，数据不存在，退出计算。
                    return;

            }
            
            if( TextBox_Level_Counsellor.Text == strLevel_Counsellor_LastMonth)
            {///顾问级别不变
                string strTemp = "Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + (Month_selected - 1).ToString().Trim() + "'";
                ///读取上个月的“月缺口金额”
                ///
                string strGap_Amount_month = Common_SXD.CHAR_MySQL("select Gap_Amount_month from Merit_Pay ",
                strTemp, Response);

                if (strGap_Amount_month == "ERROR") ///读取“缺口金额”出错，数据不存在，退出计算。
                    return;

                string strMerit_Pay_Frozen = Common_SXD.CHAR_MySQL("select Merit_Pay_Frozen from Merit_Pay ",
                strTemp, Response);

                string strTake_Home_Merit_Pay = Common_SXD.CHAR_MySQL("select Take_Home_Merit_Pay from Merit_Pay ",
                strTemp, Response);

                Update_Merit_Pay(TextBox_Level_Counsellor.Text, strGap_Amount_month, 
                    strMerit_Pay_Frozen, strTake_Home_Merit_Pay);
            }
            else///顾问级别改变了
                Init_Merit_Pay();
            
            ///测试测试本月绩效工资数据，是否存在。
            ///
#if MSSql
            string sCounter = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No) from Contract ",
                "Office like '" + TextBox_Office.Text.Trim() + "' and Student_Name like '" + TextBox_Student_Name.Text.Trim() + "'", Response);
#elif MySql
            string sCounter = Common_SXD.CHAR_MySQL_PLUS1("select count(Code_Merit_Pay) from Merit_Pay ",
                "Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + Month_selected.ToString().Trim() + "'", Response);

#endif

            if (Convert.ToInt16(sCounter) > 1)///测试测试本月绩效工资数据，是否存在。
            {   ///更新记录
                ///
                string sSql = "Update Merit_Pay Set";
                sSql += " Merit_Pay = '" +  dMerit_Pay.ToString().Trim() + "', "; /* 绩效工资 */
                sSql += " Percent_Frozen = '" +  dPercent_Frozen.ToString().Trim() + "', "; /* 冻结百分比 */
                sSql += " Quota_month = '" +  dQuota_month.ToString().Trim() + "', "; /* 月扣减合同额 */
                sSql += " Countract_Amount_month = '" +  dCountract_Amount_month.ToString().Trim() + "', "; /* 月签约额 */
                sSql += " Gap_Amount_month = '" +  dGap_Amount_month.ToString().Trim() + "', ";  /* 月缺口金额 */
                sSql += " Merit_Pay_Frozen = '" + dMerit_Pay_Frozen.ToString().Trim() + "', ";  /* 冻结工资额 */
                sSql += " Merit_Pay_UnFrozen = '" + TextBox_Merit_Pay_UnFrozen.Text.Trim() + "', ";  /* 解冻工资额 */
        
                sSql += " Take_Home_Merit_Pay = '" + dTake_Home_Merit_Pay.ToString().Trim() + "', ";
                sSql += " Level_Counsellor = '" + iLevel_Counsellor.ToString().Trim() + "' ";
            ///////////////////////////////////////


                sSql += " where Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + Month_selected.ToString().Trim() + "'";
#if MSSql
            Common_SXD.MSSql_ExecuteNonQuery(sSql, "", "打开数据库出错！", Response);
#elif MySql
            Common_SXD.MySql_ExecuteNonQuery(sSql, "", "打开数据库出错！", Response);
#endif
        
            }
            else
            {   ///插入新纪录
                ///
                Response.Clear();
                string sSql = "  insert into Merit_Pay  (Office, C_N_Employee,  Year_, Month_, Level_Counsellor, Merit_Pay, Percent_Frozen, Quota_month, Countract_Amount_month, Gap_Amount_month, Merit_Pay_Frozen, Take_Home_Merit_Pay, Merit_Pay_UnFrozen, Is_Active)  values(";
                sSql += "'" + myOffice.ToString().Trim() + "', ";
                sSql += "'" + myCounsellor.ToString().Trim() + "', ";
                sSql += "'" + Year_selected.ToString().Trim() + "', ";
                sSql += "'" + Month_selected.ToString().Trim() + "', ";
                sSql += "'" + iLevel_Counsellor.ToString().Trim() + "', ";
                sSql += "'" + dMerit_Pay.ToString().Trim() + "', ";
                sSql += "'" + dPercent_Frozen.ToString().Trim() + "', ";
                sSql += "'" + dQuota_month.ToString().Trim() + "', ";
                sSql += "'" + dCountract_Amount_month.ToString().Trim() + "', ";
                sSql += "'" + dGap_Amount_month.ToString().Trim() + "', ";
                sSql += "'" + dMerit_Pay_Frozen.ToString().Trim() + "', ";
                sSql += "'" + dTake_Home_Merit_Pay.ToString().Trim() + "', ";
                sSql += " 0, ";
                sSql += " 1 )";
#if MySql
                Common_SXD.MySql_ExecuteNonQuery(sSql, "", "打开数据库出错！", Response);
#elif MSSql
            Common_SXD.MSSql_ExecuteNonQuery(sSql, "", "打开数据库出错！", Response);
#endif
            }

        }


        ///static string tempLevel_Counsellor = "0";
        /// <summary>
        /// 绩效工资列表, 显示至今为止的全年绩效工资。
        /// </summary>
        private void List_Merit_Pay(string myOffice, string myCounsellor, string sTime)
        {
            ///string sSearchValue = TextBox_SearchValue.Text.Trim();

            ///string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            int Month_selected = 0; ///选定月份
            int Year_selected = 0; ///选定年份
            
            DateTime dNow = DateTime.Now;
            int tDay_now = DateTime.Now.Day;
            int tMonth_now = DateTime.Now.Month;
            int tYear_now = DateTime.Now.Year - 1;

            string strOffice = Request["Office"].ToString().Trim();
            strOffice = Common_SXD.get_C_Office(strOffice);
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            ///北京办公室不参加
            if(strOffice == "北京")
                return;

            string sSql;

            
            ///string sDay_Minus1Year = Convert.ToString(tYear) + "-" + Convert.ToString(tMonth) + "-" + Convert.ToString(tDay);

            ///检查年月字符串语法
            Regex Testing = new Regex("[0-9]{4}(-|/){1}[0-9]{1,2}");
            //if (Testing.IsMatch(sTime))TextBox_Year_Month.Text
            if (Testing.IsMatch(TextBox_Year_Month.Text))
            {
                //System.DateTime bonusDateTime = Convert.ToDateTime(sTime + "-1");
                System.DateTime bonusDateTime = Convert.ToDateTime(TextBox_Year_Month.Text + "-1");
                if (bonusDateTime > Convert.ToDateTime("1930-1-1"))
                {
                    Month_selected = bonusDateTime.Month;
                    Year_selected = bonusDateTime.Year;
                    
                }
            }

            ///测试
            ///Label_Series_No_Applicants.Text = "9999";
            Session.Timeout = 118;
            sSql = "select  Office, C_N_Employee, Year_, Month_, Level_Counsellor, Merit_Pay, Percent_Frozen, Quota_month, Countract_Amount_month, Gap_Amount_month, Merit_Pay_Frozen, Take_Home_Merit_Pay, Merit_Pay_UnFrozen from Merit_Pay ";
            /////查询条件
            sSql += " where  Office like '" + strOffice + "' ";
            sSql += "and   C_N_Employee like '" + strCounsellor_Writer + "' ";
            sSql += "and   Year_ like '" + Year_selected.ToString().Trim() + "' ";
            sSql += "and   Month_ <= " + Month_selected.ToString().Trim() + " ";
            /////排序方式设定开始
            sSql += " order by Month_ ASC";

            //////排序方式结束

#if MSSql
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            int nCol = sqlReader.FieldCount;
#elif MySql
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            int nCol = MysqlReader.FieldCount;
#endif

            //            }
            //清除表中的内容
            Table_List_Salary.Rows.Clear();
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    ///string temp = "";
#if MSSql
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                    string temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));

#endif

                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                
                Table_List_Salary.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;

#if MSSql
            while (sqlReader.Read())
#elif MySql
            
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
                ////////////////////////////////
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();

#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    /*
                    if (i == 4)
                    {
                        string tempLevel_Counsellor = Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString());
                        TextBox_Level_Counsellor.Text = tempLevel_Counsellor;
                    }
                    */

                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }
                
                Table_List_Salary.Rows.Add(row);
                j++;
                
            }
            
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接

#endif
            string strTemp = "Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + (Month_selected - 1).ToString().Trim() + "'";
            ///读取上个月的顾问级别
            string strLevel_Counsellor_LastMonth = Common_SXD.CHAR_MySQL("select Level_Counsellor from Merit_Pay ",
                strTemp, Response);

            if (strLevel_Counsellor_LastMonth == "ERROR") ///读取顾问等级出错，数据不存在，退出计算。
                return;

            string strGap_Amount_month = Common_SXD.CHAR_MySQL("select Gap_Amount_month from Merit_Pay ",
                strTemp, Response);

            if (strGap_Amount_month == "ERROR") ///读取“缺口金额”出错，数据不存在，退出计算。
                return;

            ///读取本月的解冻工资
            strTemp = "Office like '" + myOffice.ToString().Trim() + "' and C_N_Employee like '" + myCounsellor.ToString().Trim() + "' and " +
                "Year_ like '" + Year_selected.ToString().Trim() + "' and Month_ like '" + (Month_selected).ToString().Trim() + "'";
            string strMerit_Pay_UnFrozen = Common_SXD.CHAR_MySQL("select Merit_Pay_UnFrozen from Merit_Pay ",
                strTemp, Response);
            if (strMerit_Pay_UnFrozen == "ERROR")
                return;
            else
                dMerit_Pay_UnFrozen = Convert.ToDecimal(strMerit_Pay_UnFrozen);
            TextBox_Merit_Pay_UnFrozen.Text = dMerit_Pay_UnFrozen.ToString().Trim();


            // 如有缺口，级别 + 1
            if (Convert.ToDecimal(strGap_Amount_month) < 0)
            {
                Decimal De = Convert.ToDecimal(strLevel_Counsellor_LastMonth) + 1;
                if (De < 4)
                    TextBox_Level_Counsellor.Text = De.ToString().Trim();
                else
                    TextBox_Level_Counsellor.Text = "4";
            }
            else
                TextBox_Level_Counsellor.Text = strLevel_Counsellor_LastMonth;


            
        }

        protected void Bt_Refress_Search_Click(object sender, EventArgs e)
        {
            Common_SXD.Kill_Sleep_Threads(Response);
            ///合同签约计算
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
            Init_Tables_Contract_Qianyue();
            Init_Tables_Contract_Tuikuan();
            Init_Contract_Amount();
            
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;
            
            string strOffice = Request["Office"].ToString().Trim();
            strOffice = Common_SXD.get_C_Office(strOffice);
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            if (CheckBox_Allowed_Calculation.Checked)
            {/// 允许重新计算
                Calculation_Merit_Pay(strOffice, strCounsellor_Writer, TextBox_Year_Month.Text);
            }
            List_Merit_Pay(strOffice, strCounsellor_Writer, TextBox_Year_Month.Text);

              

            System.DateTime bonusDateTime20101201 =
    new System.DateTime(2010,        // Year
    12,            // Month
    1,            // Day
    0,            // Hour
    0,            // Minute
    0,            // Second
    0             // Millisecond
    );

            string temp = TextBox_Year_Month.Text + "-1";
            System.DateTime bonusNow = Convert.ToDateTime(temp);
            /// excel 奖金表的行数限制，分为上中下3个最大行数。
            if (bonusNow >= bonusDateTime20101201 && Max_TopPart <= 40 && Max_MiddlePart <= 16
                && Max_BottomPart <= 20)
            {
                Bt_Bonus_Exel.Enabled = true; //允许发送 excel格式的(101217版)奖金表
                /*  注释后不生成奖金 execl 文件
                SynchronousSocketClient MyClient = new SynchronousSocketClient();
                // 生成 exel 奖金文件
                MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response);
            
                 */
            }
            else
            {
                Bt_Bonus_Exel.Enabled = false;
                Response.Write("无法生成 excel 格式的(101217版)奖金表（日期不合适或者数据超出表格范围）！");

            }

        }

        protected void Bt_Bonus_Exel_Click(object sender, EventArgs e)
        {
            
        }
    }
}